CREATE PROCEDURE [dbo].[asi_GetOrderPaymentTotal]
@orderNumber nvarchar(50),
@includePmtsAppliedToLines bit,
@orderLineNumber int,
@includeUnpostedPayments bit,
@systemEntityKey uniqueidentifier
AS
DECLARE @tmpTotal decimal(18,4)
DECLARE @paymentTotal decimal(18,4)
SET @paymentTotal = 0
      
IF @orderLineNumber is not null AND @orderLineNumber > 0
BEGIN
      SELECT @tmpTotal = SUM(ISNULL(ma.Amount,0) + ISNULL(ma.DiscountTaken,0)) FROM MonetaryApplication ma
      INNER JOIN PaymentMain pm ON pm.PaymentKey = ma.SrcPaymentKey
      WHERE ma.OrderNumber = @orderNumber AND ma.OrderLineNumber = @orderLineNumber
      AND pm.SystemEntityKey = @systemEntityKey
      SET @paymentTotal = @paymentTotal + ISNULL(@tmpTotal,0)
      
      SELECT @tmpTotal = SUM(ISNULL(ma.Amount,0) + ISNULL(ma.DiscountTaken,0)) FROM MonetaryApplication ma
      INNER JOIN InvoiceLine il ON il.InvoiceLineKey = ma.SrcInvoiceLineKey
      INNER JOIN InvoiceMain im ON im.InvoiceKey = il.InvoiceKey
      WHERE ma.OrderNumber = @orderNumber AND ma.OrderLineNumber = @orderLineNumber
      AND im.SystemEntityKey = @systemEntityKey
      SET @paymentTotal = @paymentTotal + ISNULL(@tmpTotal,0)
END
ELSE
BEGIN
      IF @includePmtsAppliedToLines = 1
      BEGIN -- Omit the order line number filter...
            SELECT @tmpTotal = SUM(ISNULL(ma.Amount,0) + ISNULL(ma.DiscountTaken,0)) FROM MonetaryApplication ma
            INNER JOIN PaymentMain pm ON pm.PaymentKey = ma.SrcPaymentKey
            WHERE ma.OrderNumber = @orderNumber
            AND pm.SystemEntityKey = @systemEntityKey
            SET @paymentTotal = @paymentTotal + ISNULL(@tmpTotal,0)
            SELECT @tmpTotal = SUM(ISNULL(ma.Amount,0) + ISNULL(ma.DiscountTaken,0)) FROM MonetaryApplication ma
            INNER JOIN InvoiceLine il ON il.InvoiceLineKey = ma.SrcInvoiceLineKey
            INNER JOIN InvoiceMain im ON im.InvoiceKey = il.InvoiceKey
            WHERE ma.OrderNumber = @orderNumber
            AND im.SystemEntityKey = @systemEntityKey
            SET @paymentTotal = @paymentTotal + ISNULL(@tmpTotal,0)
      END
      ELSE
      BEGIN -- Add the order line number is null or 0 filter...
            SELECT @tmpTotal = SUM(ISNULL(ma.Amount,0) + ISNULL(ma.DiscountTaken,0)) FROM MonetaryApplication ma
            INNER JOIN PaymentMain pm ON pm.PaymentKey = ma.SrcPaymentKey
            WHERE ma.OrderNumber = @orderNumber AND (ma.OrderLineNumber is null OR ma.OrderLineNumber <=0)
            AND pm.SystemEntityKey = @systemEntityKey
            SET @paymentTotal = @paymentTotal + ISNULL(@tmpTotal,0)
            SELECT @tmpTotal = SUM(ISNULL(ma.Amount,0) + ISNULL(ma.DiscountTaken,0)) FROM MonetaryApplication ma
            INNER JOIN InvoiceLine il ON il.InvoiceLineKey = ma.SrcInvoiceLineKey
            INNER JOIN InvoiceMain im ON im.InvoiceKey = il.InvoiceKey
            WHERE ma.OrderNumber = @orderNumber AND (ma.OrderLineNumber is null OR ma.OrderLineNumber <=0)
            AND im.SystemEntityKey = @systemEntityKey
            SET @paymentTotal = @paymentTotal + ISNULL(@tmpTotal,0)
      END
END
IF @includeUnpostedPayments = 1
BEGIN -- get the work applications...
      IF @orderLineNumber is not null AND @orderLineNumber > 0
      BEGIN
            SELECT @tmpTotal = SUM(ISNULL(wma.Amount,0) + ISNULL(wma.DiscountTaken,0)) FROM WorkMonetaryApplication wma
            INNER JOIN WorkPayment wpm ON wpm.WorkPaymentKey = wma.SrcPaymentKey
            WHERE wma.OrderNumber = @orderNumber AND wma.OrderLineNumber = @orderLineNumber
            AND wpm.SystemEntityKey = @systemEntityKey
            SET @paymentTotal = @paymentTotal + ISNULL(@tmpTotal,0)
            
            SELECT @tmpTotal = SUM(ISNULL(wma.Amount,0) + ISNULL(wma.DiscountTaken,0)) FROM WorkMonetaryApplication wma
            INNER JOIN InvoiceLine il ON il.InvoiceLineKey = wma.SrcInvoiceLineKey
            INNER JOIN InvoiceMain im ON im.InvoiceKey = il.InvoiceKey
            WHERE wma.OrderNumber = @orderNumber AND wma.OrderLineNumber = @orderLineNumber
            AND im.SystemEntityKey = @systemEntityKey
            SET @paymentTotal = @paymentTotal + ISNULL(@tmpTotal,0)
      END
      ELSE
      BEGIN
            IF @includePmtsAppliedToLines = 1
            BEGIN -- Omit the order line number filter...
                  SELECT @tmpTotal = SUM(ISNULL(wma.Amount,0) + ISNULL(wma.DiscountTaken,0)) FROM WorkMonetaryApplication wma
                  INNER JOIN WorkPayment wpm ON wpm.WorkPaymentKey = wma.SrcPaymentKey
                  WHERE wma.OrderNumber = @orderNumber
                  AND wpm.SystemEntityKey = @systemEntityKey
                  SET @paymentTotal = @paymentTotal + ISNULL(@tmpTotal,0)
      
                  SELECT @tmpTotal = SUM(ISNULL(wma.Amount,0) + ISNULL(wma.DiscountTaken,0)) FROM WorkMonetaryApplication wma
                  INNER JOIN InvoiceLine il ON il.InvoiceLineKey = wma.SrcInvoiceLineKey
                  INNER JOIN InvoiceMain im ON im.InvoiceKey = il.InvoiceKey
                  WHERE wma.OrderNumber = @orderNumber
                  AND im.SystemEntityKey = @systemEntityKey
                  SET @paymentTotal = @paymentTotal + ISNULL(@tmpTotal,0)
            END
            ELSE
            BEGIN -- Add the order line number is null or 0 filter...
                  SELECT @tmpTotal = SUM(ISNULL(wma.Amount,0) + ISNULL(wma.DiscountTaken,0)) FROM WorkMonetaryApplication wma
                  INNER JOIN WorkPayment wpm ON wpm.WorkPaymentKey = wma.SrcPaymentKey
                  WHERE wma.OrderNumber = @orderNumber AND (wma.OrderLineNumber is null OR wma.OrderLineNumber <=0)
                  AND wpm.SystemEntityKey = @systemEntityKey
                  SET @paymentTotal = @paymentTotal + ISNULL(@tmpTotal,0)
      
                  SELECT @tmpTotal = SUM(ISNULL(wma.Amount,0) + ISNULL(wma.DiscountTaken,0)) FROM WorkMonetaryApplication wma
                  INNER JOIN InvoiceLine il ON il.InvoiceLineKey = wma.SrcInvoiceLineKey
                  INNER JOIN InvoiceMain im ON im.InvoiceKey = il.InvoiceKey
                  WHERE wma.OrderNumber = @orderNumber AND (wma.OrderLineNumber is null OR wma.OrderLineNumber <=0)
                  AND im.SystemEntityKey = @systemEntityKey
                  SET @paymentTotal = @paymentTotal + ISNULL(@tmpTotal,0)
            END
      END
END
select @paymentTotal

GO